<?php

class CalendarQuery
{
	public $pdo;
	
	public function get_entries($user,$offset)
	{
		$week_start = mktime(0,0,0);  $week_start += $offset * 7 * 24 * 60 * 60;
		$week_end = mktime(23,59,59); $week_end   += $offset * 7 * 24 * 60 * 60;
		while(date("N",$week_start) != 1)
			$week_start -= 24 * 60 * 60;
		while(date("N",$week_end) != 7)
			$week_end += 24 * 60 * 60;
	
		$stmt = $this->pdo->prepare("SELECT DISTINCT 'lectures' AS TYPE,
			                                   extract(epoch FROM LECTURES.START_TIME) AS START_TIME,
			                                   extract(epoch FROM LECTURES.END_TIME) AS END_TIME,
			                                   LECTURES.LOCATION,
			                                   COURSES.NAME AS COURSE
			                   FROM COURSES, INSTANCES, GROUPS, LECTURES, REGISTRATIONS
			                   WHERE REGISTRATIONS.STUDENT = :userid
			                     AND LECTURES.START_TIME >= to_timestamp(:week_start)
			                     AND LECTURES.END_TIME <= to_timestamp(:week_end)
			                     AND LECTURES.INSTANCE = INSTANCES.ID
			                     AND GROUPS.ID = REGISTRATIONS.GROUPS
			                     AND INSTANCES.ID = GROUPS.INSTANCE
			                     AND COURSES.ID = INSTANCES.COURSE
			                   UNION
			                   SELECT DISTINCT 'exams' AS TYPE,
			                                   extract(epoch FROM EXAMS.START_TIME) AS START_TIME,
			                                   extract(epoch FROM EXAMS.END_TIME) AS END_TIME,
			                                   EXAMS.LOCATION,
			                                   COURSES.NAME AS COURSE
			                   FROM COURSES, INSTANCES, GROUPS, EXAMS, REGISTRATIONS
			                   WHERE REGISTRATIONS.STUDENT = :userid
			                     AND EXAMS.START_TIME >= to_timestamp(:week_start)
			                     AND EXAMS.END_TIME <= to_timestamp(:week_end)
			                     AND EXAMS.INSTANCE = INSTANCES.ID
			                     AND GROUPS.ID = REGISTRATIONS.GROUPS
			                     AND INSTANCES.ID = GROUPS.INSTANCE
			                     AND COURSES.ID = INSTANCES.COURSE
			                   UNION
			                   SELECT DISTINCT 'meetings' AS TYPE,
			                                   extract(epoch FROM MEETINGS.START_TIME) AS START_TIME,
			                                   extract(epoch FROM MEETINGS.END_TIME) AS END_TIME,
			                                   MEETINGS.LOCATION,
			                                   COURSES.NAME AS COURSE
			                   FROM COURSES, INSTANCES, GROUPS, MEETINGS, REGISTRATIONS
			                   WHERE REGISTRATIONS.STUDENT = :userid
			                     AND MEETINGS.START_TIME >= to_timestamp(:week_start)
			                     AND MEETINGS.END_TIME <= to_timestamp(:week_end)
			                     AND MEETINGS.GROUPS = GROUPS.ID
			                     AND GROUPS.ID = REGISTRATIONS.GROUPS
			                     AND INSTANCES.ID = GROUPS.INSTANCE
			                     AND COURSES.ID = INSTANCES.COURSE
			                   UNION
			                   SELECT DISTINCT 'taught' AS TYPE,
			                                   extract(epoch FROM LECTURES.START_TIME) AS START_TIME,
			                                   extract(epoch FROM LECTURES.END_TIME) AS END_TIME,
			                                   LECTURES.LOCATION,
			                                   COURSES.NAME AS COURSE
			                   FROM COURSES, INSTANCES, LECTURES
			                   WHERE INSTANCES.TEACHER = :userid
			                     AND LECTURES.START_TIME >= to_timestamp(:week_start)
			                     AND LECTURES.END_TIME <= to_timestamp(:week_end)
			                     AND LECTURES.INSTANCE = INSTANCES.ID
			                     AND COURSES.ID = INSTANCES.COURSE
			                   UNION
			                   SELECT DISTINCT 'supervised' AS TYPE,
			                                   extract(epoch FROM MEETINGS.START_TIME) AS START_TIME,
			                                   extract(epoch FROM MEETINGS.END_TIME) AS END_TIME,
			                                   MEETINGS.LOCATION,
			                                   COURSES.NAME AS COURSE
			                   FROM COURSES, INSTANCES, GROUPS, MEETINGS
			                   WHERE GROUPS.TEACHER = :userid
			                     AND MEETINGS.START_TIME >= to_timestamp(:week_start)
			                     AND MEETINGS.END_TIME <= to_timestamp(:week_end)
			                     AND MEETINGS.GROUPS = GROUPS.ID
			                     AND INSTANCES.ID = GROUPS.INSTANCE
			                     AND COURSES.ID = INSTANCES.COURSE
			                   ORDER BY START_TIME ASC");
		$stmt->execute(array(":userid"=>$user,":week_start"=>$week_start,":week_end"=>$week_end));
	
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}
}

?>
